package com.houdoudou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.houdoudou.bean.Product;

public class ProductDao {
	 Connection conn=null;
	 

	public ProductDao(Connection conn) {
		super();
		this.conn = conn;
	}

	public  int create( Product product ) throws SQLException{	
		int result=0;
		  PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
		   String sql=null;
	        try {	        	
	        	sql = "insert  into product (name,sort,isAuth,owner,supplier,detail,dir,sale,isHot,isCold,isNormal,type,picNum) "
	        			+ "values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
	        	pstm=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
	        	pstm.setString(1, product.getName());
	        	pstm.setInt(2, product.getSort());
	        	pstm.setInt(3, product.getIsAuth());
	        	pstm.setString(4, product.getOwner()); 
	        	pstm.setString(5, product.getSupplier());  
	        	pstm.setString(6, product.getDetail());
	        	pstm.setString(7, product.getDir());
	        	pstm.setInt(8, product.getSale());
	        	pstm.setInt(9, product.getIsHot());
	        	pstm.setInt(10, product.getIsCold());
	        	pstm.setInt(11, product.getIsNormal());
	        	pstm.setInt(12, product.getType());
	        	pstm.setInt(13, product.getPicNum());
				pstm.executeUpdate();
				rs=pstm.getGeneratedKeys();
				rs.next();
				result=rs.getInt(1);
					
		   		
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}			
		return result;
	}
	
	public  void delete(Product product) throws SQLException{	
		
		  PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
		   String sql=null;
	        try {
	        	sql = "delete  from product where id="+product.getId()+"";		        	
				stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
				stm.executeUpdate(sql);
				
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}			
		
	}
	
	public  int update( Product product ) throws SQLException{	
		int result=0;
		  PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
		   String sql=null;
	        try {
	        	sql = "update product set name=?,sort=?,isAuth=?,owner=?,supplier=?,"
	        			+ "detail=?,dir=?,sale=?,isHot=?,isCold=?,isNormal=?,"
	        			+ "type=?,picNum=? where id = ?";
	        	pstm=conn.prepareStatement(sql);	        	
	        	pstm.setString(1, product.getName());
	        	pstm.setInt(2, product.getSort());
	        	pstm.setInt(3, product.getIsAuth());
	        	pstm.setString(4, product.getOwner()); 
	        	pstm.setString(5, product.getSupplier());  
	        	pstm.setString(6, product.getDetail());
	        	pstm.setString(7, product.getDir());
	        	pstm.setInt(8, product.getSale());
	        	pstm.setInt(9, product.getIsHot());
	        	pstm.setInt(10, product.getIsCold());
	        	pstm.setInt(11, product.getIsNormal());
	        	pstm.setInt(12, product.getType());
	        	pstm.setInt(13, product.getPicNum());
	        	pstm.setInt(14, product.getId());
				result=pstm.executeUpdate();
				
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}			
		return result;
	}
	
	

	public List< Product> search( String sql) throws SQLException{	
		List< Product> productes= new ArrayList<Product>();
		  PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
	
	        try {
	        		        	
				stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
				rs=stm.executeQuery(sql);
				while(rs.next()){
					Product product = new Product();
					product.setId(rs.getInt("id"));
					product.setName(rs.getString("name"));
					product.setSort(rs.getInt("sort"));
					product.setIsAuth(rs.getInt("isAuth"));
					product.setOwner(rs.getString("owner"));
					product.setSupplier(rs.getString("supplier"));
					product.setDetail(rs.getString("detail"));
					product.setDir(rs.getString("dir"));
					product.setSale(rs.getInt("sale"));
					product.setIsHot(rs.getInt("isHot"));
					product.setIsCold(rs.getInt("isCold"));
					product.setIsNormal(rs.getInt("isNormal"));
					product.setType(rs.getInt("type"));
					product.setPicNum(rs.getInt("picNum"));
					productes.add(product);
				}
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}			
		return productes;
	}	
	
	 public Product selectById(Product product) throws Exception{
		   String sql="select * from product  where id = "+ product.getId()+" ";
		   List<Product> productes= search(sql);
		   Product productExist = null;
		   if(productes.size()>0){ productExist= productes.get(0); 
		   }
		   return productExist;
	   }
	 
		public static  String  getSearchSql(Product product){
			String sql="select * from  product";
			int count=0;
			if(product.getId()!=0){ sql="select * from product  where id = "+product.getId()+""; return sql;
			 }else{
				
				if(!product.getSupplier().isEmpty()) {	
					if(count==0) {sql= sql+" where supplier = "+product.getSupplier();
					}else{
						sql= sql+" and  supplier = "+product.getSupplier();
					  }  
					  count++; }
				if(!product.getOwner().isEmpty()) {	
					if(count==0) {sql= sql+" where owner = "+product.getOwner();
					}else{
						sql= sql+" and  owner = "+product.getOwner();
					  }  
					  count++; }
				if(product.getSort()!=0) {	
					if(count==0) {sql= sql+" where sort = "+product.getSort();
					}else{
						sql= sql+" and  sort = "+product.getSort();
					  }  
					  count++; }
				
			}
			System.out.println(sql);
			return sql;
		}
}
